﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Windows.Forms;
using System.Data;
namespace libry
{
    class sqlUser
    {
        static SQLiteConnection con = new SQLiteConnection("data source = E:\\vs2019\\sqlite\\libry.db");

        public static SQLiteConnection Con { get => con; set => con = value; }

        //修改book_borrow
        public static int ChangeBookBorrow(int id, string changetext)
        {
            bool isOpen = false;
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                    isOpen = true;
                }
                string query = "UPDATE book_borrow SET " + changetext + " WHERE id =" + id;//sql语句
                SQLiteCommand cmd = new SQLiteCommand(query, con);
                int affected = cmd.ExecuteNonQuery();
                if (affected > 0)
                {
                    //MessageBox.Show("修改book_borrow成功！");
                }
                else
                {
                    // MessageBox.Show("修改book_borrow表失败！");
                }
                if (isOpen)
                {
                    con.Close();
                }
                return affected;
            }
            catch (Exception ex)
            {
                MessageBox.Show("修改book_borrow表失败！" + ex);
                if (isOpen)
                {
                    con.Close();
                }
                return 0;
            }
        }

        //根据id获取book_located
        public static DataTable getBookLocatedById(String id)
        {
            try
            {
                con.Open();
                string query = "SELECT * FROM book_located WHERE id = '" + id + "'";
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, con);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                con.Close();
                return dt;
            }
            catch { con.Close(); return null; }
        }
        //增加借阅表
        public static void addBookBorrow(int id, int userId, int bookid, String bookLocatedId, String beginTime, String entTime, String returned)
        {
            bool isOpen = false;
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                    isOpen = true;

                }
                String sql = "INSERT INTO book_borrow(id,bookid,userId,bookLocatedId,beginTime,endTime,returned) VALUES ('" + id + "','" + bookid + "','" + userId + "','" + bookLocatedId + "','" + beginTime + "','" + entTime + "','" + returned + "')";//sql语句"
                SQLiteCommand comm = new SQLiteCommand(sql, con);
                if (comm.ExecuteNonQuery() > 0)
                {
                    // MessageBox.Show("添加借阅表成功");
                }
                else
                {
                    //  MessageBox.Show("添加借阅表失败");
                }
                if (isOpen)
                {
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("增加借阅表失败\r\n" + ex.Message);
                if (isOpen)
                {
                    con.Close();
                }
            }
        }
        //根据id查询book_borrow
        public static DataTable getBookBorrowById(int id)
        {
            try
            {
                con.Open();
                String sql = "SELECT * FROM book_borrow WHERE id =" + id;
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, con);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                con.Close();
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show("根据id查询book_borrow出错" + ex.Message);
                con.Close();
                return null;
            }
        }
        //删除
        public static void deleteBookBorrowById(int id)
        {
            try
            {
                con.Open();
                String sql = "DELETE FORM book_borrow WHERE id = " + id;
                SQLiteCommand command = new SQLiteCommand(sql, con);
                if (command.ExecuteNonQuery() > 0)
                {
                    // MessageBox.Show("删除book_borrow成功");
                }
                else
                {
                    //  MessageBox.Show("删除book_borrow失败");
                }
                con.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }
        //修改
        public static void ChangeBookLocated(string id, string changetext)
        {
            bool isOpen = false;
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                    isOpen = true;
                }
                string query = "UPDATE book_located SET " + changetext + " WHERE " + "id='" + id + "'";//sql语句
                SQLiteCommand cmd = new SQLiteCommand(query, con);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    //  MessageBox.Show("修改book_located成功！");
                }
                else
                {
                    // MessageBox.Show("修改book_located失败！");
                }
                if (isOpen)
                {
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                if (isOpen)
                {
                    con.Close();
                }
            }
        }
        public static DataTable getBookBorrowAll()
        {
            try
            {
                con.Open();
                String sql = "SELECT * FROM book_borrow";
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, con);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                con.Close();
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
                return null;
            }
        }
        //用于显示在界面上的查询,但是returned依旧是0和1
        public static DataTable getBookBorrow(String table, String bookName, String userEmail, String returned)
        {
            try
            {
                con.Open();
                SQLiteDataAdapter bookBorrowAdapter = new SQLiteDataAdapter("SELECT * FROM " + table, con);
                DataTable dt = new DataTable();
                bookBorrowAdapter.Fill(dt);
                con.Close();
                DataTable res = dt.Clone();
                DataRow[] rows = dt.Select("bookname like '%" + bookName + "%'and userEmail = '" + userEmail + "'and returned = '" + returned + "'");
                foreach (DataRow row in rows)
                {
                    res.ImportRow(row);
                }
                return res;
            }
            catch (Exception ex)
            {
                MessageBox.Show("界面显示查询出错\r\n" + ex.Message);
                con.Close();
                return null;
            }
        }
        public static DataRow[] queryBookBorrow(String table, String bookName, String userEmail, String returned)
        {
            try
            {
                con.Open();
                SQLiteDataAdapter bookBorrowAdapter = new SQLiteDataAdapter("SELECT * FROM " + table, con);
                DataTable dt = new DataTable();
                bookBorrowAdapter.Fill(dt);
                con.Close();
                DataRow[] rows = dt.Select("bookname like '%" + bookName + "%'and userEmail = '" + userEmail + "'and returned = '" + returned + "'");
                return rows;
            }
            catch (Exception ex)
            {
                MessageBox.Show("界面显示查询出错\r\n" + ex.Message);
                con.Close();
                return null;
            }
        }
        public static DataTable getUserById(int id)
        {
            try
            {
                con.Open();
                SQLiteDataAdapter adapter = new SQLiteDataAdapter("SELECT * FROM user WHERE userId = " + id, con);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                con.Close();
                return dt;

            }
            catch (Exception ex)
            {
                MessageBox.Show("根据id查询用户出错\r\n" + ex.Message);
                con.Close();
                return null;
            }
        }
        //修改user
        public static int ChangeUser(int id, string changetext)
        {
            bool isOpen = false;
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                    isOpen = true;
                }
                string query = "UPDATE user SET " + changetext + " WHERE userId = " + id;//sql语句
                SQLiteCommand cmd = new SQLiteCommand(query, con);
                int affected = cmd.ExecuteNonQuery();
                if (affected > 0)
                {
                    //MessageBox.Show("修改user成功！");
                }
                else
                {
                    // MessageBox.Show("修改user表失败！");
                }
                if (isOpen)
                {
                    con.Close();
                }
                return affected;
            }
            catch (Exception ex)
            {
                MessageBox.Show("修改user表失败！" + ex);
                if (isOpen)
                {
                    con.Close();
                }
                return 0;
            }
        }

    }

}
